USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAttributes_Save]    Script Date: 05/25/2011 07:00:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [classified].[ClassifiedAttributes_Save]
	@CategoryID bigint,
	@AttributeID bigint,
	@AttributeTitle varchar(100),
	@AttributeDescription varchar(256),
	@Required bit,
	@IsCustom bit,
	@AttributeValueNames attributevalues readonly
AS
BEGIN

	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION TR

	
	DECLARE @NEWID bigint;
	
    UPDATE classified.ClassifiedAttributes
    SET AttributeTitle = @AttributeTitle,
		AttributeDescription = @AttributeDescription,
		Required = @Required   
	WHERE CategoryID = @CategoryID AND AttributeID = @AttributeID AND IsCustom = @IsCustom
	
	IF @@ROWCOUNT = 0
	BEGIN
		--getting the max sort order
		DECLARE @MaxOrder tinyint;
		SELECT @MaxOrder = ISNULL(MAX(SortOrder), 0) FROM classified.ClassifiedAttributes
		WHERE CategoryID = @CategoryID
		--inserting
		INSERT INTO classified.ClassifiedAttributes
		VALUES (@CategoryID, @AttributeTitle, @AttributeDescription, @Required, @MaxOrder+1, @IsCustom)
		Set @NEWID = SCOPE_IDENTITY()
	END
	
	-- working with attributeValues
	IF (@AttributeID = 0)
		BEGIN
			MERGE INTO classified.ClassifiedAttributeValues cav
				USING @AttributeValueNames avn ON (cav.AttributeID = @NEWID AND cav.AttributeValue = avn.AttributeValue)
			WHEN MATCHED THEN
				UPDATE SET cav.AttributeValueName = avn.AttributeValueName
			WHEN NOT MATCHED THEN
				INSERT (CategoryID, AttributeID, AttributeValue, AttributeValueName)
				VALUES (@CategoryID, @NEWID, avn.AttributeValue, avn.AttributeValueName);
		END
	ELSE
		BEGIN
			DELETE FROM classified.ClassifiedAttributeValues
			WHERE 
			AttributeValue NOT IN (SELECT AttributeValue FROM @AttributeValueNames)
			AND CategoryID = @CategoryID AND AttributeID = @AttributeID;
			
			MERGE INTO classified.ClassifiedAttributeValues cav
				USING @AttributeValueNames avn 
				ON (CategoryID = @CategoryID AND cav.AttributeID = @AttributeID AND cav.AttributeValue = avn.AttributeValue)
			WHEN MATCHED THEN
				UPDATE SET cav.AttributeValueName = avn.AttributeValueName
			WHEN NOT MATCHED THEN
				INSERT (CategoryID, AttributeID, AttributeValue, AttributeValueName)
				VALUES (@CategoryID, @AttributeID, avn.AttributeValue, avn.AttributeValueName);
		END
		
		COMMIT TRANSACTION TR
	END TRY
	BEGIN CATCH

		ROLLBACK TRANSACTION TR

	END CATCH

END




GO


